package cn.shenlong.util;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.ParserConfigurationException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.SAXException;

/**
 * xls文件操作类
 * 
 * @author zjs
 * 
 */
public class XLSTools {

	/**
	 * 读取xls
	 * 
	 * @param path
	 * @return
	 * @throws BiffException
	 * @throws IOException
	 */
	@SuppressWarnings("unchecked")
	public List readXLS(String path) throws BiffException, IOException {
		String type = path.substring(path.lastIndexOf(".") + 1, path.length());

		if (!type.toLowerCase().equals("XLS".toLowerCase())) {
			try {
				return readXLSX(path);
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
		}

		List list = new ArrayList();
		List temp = null;
		Workbook book = Workbook.getWorkbook(new File(path));
		Sheet[] sheets = book.getSheets();

		for (int count = 0; count < sheets.length; count++) {
			Sheet sheet = sheets[count];

			if (sheet.getRows() > 25000) {// 超出25000条记录退出
				list.clear();
				book.close();
				break;
			}
			for (int i = 0; i < sheet.getRows(); i++) {
				Cell[] row = sheet.getRow(i);
				if (isEmpty(row)) {
					temp = new ArrayList();
					for (int j = 0; j < row.length; j++) {
						temp.add(row[j].getContents());
					}
					list.add(temp);
				}
			}
		}
		book.close();
		return list;
	}

	/**
	 * 读取xlsx(2007)
	 * 
	 * @param path
	 * @return
	 * @throws BiffException
	 * @throws IOException
	 * @throws ParserConfigurationException
	 * @throws SAXException
	 */
	@SuppressWarnings("unchecked")
	public List readXLSX(String path) throws BiffException, IOException,
			SAXException, ParserConfigurationException {
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(path);
		List all = new ArrayList();
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++) {
			XSSFSheet xSheet = xwb.getSheetAt(numSheet);
			if (xSheet == null) {
				continue;
			}

			// 循环行Row
			for (int rowNum = 0; rowNum <= xSheet.getLastRowNum(); rowNum++) {
				XSSFRow xRow = xSheet.getRow(rowNum);
				if (xRow == null) {
					continue;
				}
				List list = new ArrayList();
				// 循环列Cell
				for (int cellNum = 0; cellNum <= xRow.getLastCellNum(); cellNum++) {
					XSSFCell xCell = xRow.getCell(cellNum);

					if (xCell == null) {
						continue;
					}
					list.add(xCell);

				}
				all.add(list);
			}
		}
		return all;
	}

	/**
	 * 写入xls
	 * 
	 * @param path
	 * @return
	 * @throws IOException
	 * @throws WriteException
	 * @throws IOException
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public boolean writeXL(List list, String str1,
			HttpServletResponse response, String[] titlesTemp)
			throws WriteException, IOException {
		boolean flag = false;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("example");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell[] = null;
		String[] titles = null;
		try {
			cell = new HSSFCell[titlesTemp.length];
			titles = titlesTemp;
			for (short i = 0; i < cell.length; i++) {
				cell[i] = row.createCell(i);
				// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
				// cell[i].setEncoding(HSSFCell.ENCODING_UTF_16);
			}
			for (int i = 0; i < titles.length; i++) {
				cell[i].setCellValue(titles[i]);
			}
			for (int i = 0; i < list.size(); i++) {
				Object[] listTemp = (Object[]) list.get(i);
				HSSFRow datarow = sheet.createRow(i + 1);
				HSSFCell data[] = new HSSFCell[titles.length];
				// System.out.println(titles.length);
				// System.out.println(listTemp.size());
				for (short j = 0; j < listTemp.length; j++) {
					data[j] = datarow.createCell(j);
					// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
					// data[j].setEncoding(HSSFCell.ENCODING_UTF_16);
					data[j].setCellValue(listTemp[j] + "");
				}
			}
			OutputStream os = response.getOutputStream();
			response.setContentType("application/vnd.ms-excel;charset=gbk");
			response.setHeader("Content-Disposition",
					"attachment; filename=temp.xls");
			workbook.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 写入xls
	 * 
	 * @param path
	 * @return
	 * @throws IOException
	 * @throws WriteException
	 * @throws IOException
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public boolean writesendXLSend(List list, String str1,
			HttpServletResponse response, String[] titlesTemp)
			throws WriteException, IOException {
		boolean flag = false;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("example");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell[] = null;
		String[] titles = null;
		try {
			cell = new HSSFCell[titlesTemp.length];
			titles = titlesTemp;
			for (short i = 0; i < cell.length; i++) {
				cell[i] = row.createCell(i);
				// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
				// cell[i].setEncoding(HSSFCell.ENCODING_UTF_16);
			}
			for (int i = 0; i < titles.length; i++) {
				cell[i].setCellValue(titles[i]);
			}
			for (int i = 0; i < list.size(); i++) {
				Object[] listTemp = (Object[]) list.get(i);

				HSSFRow datarow = sheet.createRow(i + 1);
				HSSFCell data[] = new HSSFCell[listTemp.length];

				for (short j = 0; j < listTemp.length; j++) {
					if (j > 5) {
						break;
					}
					data[j] = datarow.createCell(j);
					// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
					// data[j].setEncoding(HSSFCell.ENCODING_UTF_16);
					if (null == listTemp[j]) {
						listTemp[j] = "";
					}
					String str = (String) listTemp[j].toString();
					if (j == 1) {
						str = str.substring(0, 18);
					} else if (j == 3) {
						str = str == "0" ? "未发送" : "已发送";
					} else {
						str = str == null ? " " : str;
					}
					if (str.toLowerCase().equals("null".toLowerCase())) {
						str = "";
					}
					System.out.println(str);
					data[j].setCellValue(str + "");
					str = "";
				}

			}
			OutputStream os = response.getOutputStream();
			response.setContentType("application/vnd.ms-excel;charset=gbk");
			response.setHeader("Content-Disposition",
					"attachment; filename=temp.xls");
			workbook.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 写入xls
	 * 
	 * @param path
	 * @return
	 * @throws IOException
	 * @throws WriteException
	 * @throws IOException
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public boolean writesendXLReceive(List list, String str1,
			HttpServletResponse response, String[] titlesTemp)
			throws WriteException, IOException {
		boolean flag = false;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("example");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell[] = null;
		String[] titles = null;
		try {
			cell = new HSSFCell[titlesTemp.length];
			titles = titlesTemp;
			for (short i = 0; i < cell.length; i++) {
				cell[i] = row.createCell(i);
				// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
				// cell[i].setEncoding(HSSFCell.ENCODING_UTF_16);
			}
			for (int i = 0; i < titles.length; i++) {
				cell[i].setCellValue(titles[i]);
			}
			for (int i = 0; i < list.size(); i++) {
				Object[] listTemp = (Object[]) list.get(i);

				HSSFRow datarow = sheet.createRow(i + 1);
				HSSFCell data[] = new HSSFCell[listTemp.length];

				for (short j = 0; j < listTemp.length; j++) {
					if (j > 2) {
						break;
					}
					data[j] = datarow.createCell(j);
					// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
					// data[j].setEncoding(HSSFCell.ENCODING_UTF_16);
					if (null == listTemp[j]) {
						listTemp[j] = "";
					}
					String str = (String) listTemp[j].toString();
					if (j == 1) {
						str = str.substring(0, 18);
					} else if (j == 3) {
						str = str == "0" ? "未发送" : "已发送";
					} else {
						str = str == null ? " " : str;
					}
					if (str.toLowerCase().equals("null".toLowerCase())) {
						str = "";
					}
					System.out.println(str);
					data[j].setCellValue(str + "");
					str = "";
				}

			}
			OutputStream os = response.getOutputStream();
			response.setContentType("application/vnd.ms-excel;charset=gbk");
			response.setHeader("Content-Disposition",
					"attachment; filename=temp.xls");
			workbook.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 写入xls
	 * 
	 * @param path
	 * @return
	 * @throws IOException
	 * @throws WriteException
	 * @throws IOException
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public boolean writesendXLDel(List list, String str1,
			HttpServletResponse response, String[] titlesTemp)
			throws WriteException, IOException {
		boolean flag = false;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("example");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell[] = null;
		String[] titles = null;
		try {
			cell = new HSSFCell[titlesTemp.length];
			titles = titlesTemp;
			for (short i = 0; i < cell.length; i++) {
				cell[i] = row.createCell(i);
				// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
				// cell[i].setEncoding(HSSFCell.ENCODING_UTF_16);
			}
			for (int i = 0; i < titles.length; i++) {
				cell[i].setCellValue(titles[i]);
			}
			for (int i = 0; i < list.size(); i++) {
				Object[] listTemp = (Object[]) list.get(i);

				HSSFRow datarow = sheet.createRow(i + 1);
				HSSFCell data[] = new HSSFCell[listTemp.length];

				for (short j = 0; j < listTemp.length; j++) {
					if (j > 5) {
						break;
					}
					data[j] = datarow.createCell(j);
					// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
					// data[j].setEncoding(HSSFCell.ENCODING_UTF_16);
					if (null == listTemp[j]) {
						listTemp[j] = "";
					}
					String str = (String) listTemp[j].toString();
					if (j == 1) {
						str = str.substring(0, 18);
					} else if (j == 4) {
						str = str == "0" ? "接收" : "发送";
					} else {
						str = str == null ? " " : str;
					}
					if (str.toLowerCase().equals("null".toLowerCase())) {
						str = "";
					}
					System.out.println(str);
					data[j].setCellValue(str + "");
					str = "";
				}

			}
			OutputStream os = response.getOutputStream();
			response.setContentType("application/vnd.ms-excel;charset=gbk");
			response.setHeader("Content-Disposition",
					"attachment; filename=temp.xls");
			workbook.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 判断行是否为空
	 * 
	 * @return
	 */
	public boolean isEmpty(Cell[] cells) {
		boolean flag = true;
		int columnsNull = 0;// 空行
		for (int i = 0; i < cells.length; i++) {
			if (cells[i].getContents().length() == 0) {
				columnsNull++;
			}
		}
		if (columnsNull == cells.length) {
			flag = false;
		}
		return flag;
	}

	public static String getPath(String pathTemp) {
		String path = "";
		try {
			int last = -1;
			last = pathTemp.lastIndexOf("\\");
			path = pathTemp.substring(0, last);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return path;
	}

	/**
	 * 写入xls
	 * 
	 * @param path
	 * @return
	 * @throws IOException
	 * @throws WriteException
	 * @throws IOException
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public boolean writeXLS(List list, HttpServletResponse response,
			String[] titlesTemp, String name) throws WriteException,
			IOException {
		boolean flag = false;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("example");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell[] = null;
		String[] titles = null;
		try {
			cell = new HSSFCell[titlesTemp.length];
			titles = titlesTemp;
			for (short i = 0; i < cell.length; i++) {
				cell[i] = row.createCell(i);
				// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
				// cell[i].setEncoding(HSSFCell.ENCODING_UTF_16);
				cell[i].setCellType(HSSFCell.CELL_TYPE_STRING);
			}
			for (int i = 0; i < titles.length; i++) {
				cell[i].setCellValue(titles[i]);
			}
			for (int i = 0; i < list.size(); i++) {
				Object[] listTemp = (Object[]) list.get(i);
				HSSFRow datarow = sheet.createRow(i + 1);
				HSSFCell data[] = new HSSFCell[titles.length];
				for (short j = 0; j < listTemp.length; j++) {
					data[j] = datarow.createCell(j);
					// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
					// data[j].setEncoding(HSSFCell.ENCODING_UTF_16);
					if (listTemp[j] == null) {
						listTemp[j] = "";
					}
					data[j].setCellValue(listTemp[j] + "");
				}
			}
			OutputStream os = response.getOutputStream();
			response.setContentType("application/vnd.ms-excel;charset=gbk");
			String stcName = new String(name.getBytes(), "iso8859-1"); // 设置中文文件名
			response.setHeader("Content-Disposition", "attachment; filename="
					+ stcName + ".xls");
			workbook.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 写入xls库存
	 * 
	 * @param path
	 * @return
	 * @throws IOException
	 * @throws WriteException
	 * @throws IOException
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	public boolean writeXLStock(List list, String str1,
			HttpServletResponse response, String[] titlesTemp)
			throws WriteException, IOException {
		boolean flag = false;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("example");
		HSSFRow row = sheet.createRow((short) 0);
		HSSFCell cell[] = null;
		String[] titles = null;
		try {
			cell = new HSSFCell[titlesTemp.length];
			titles = titlesTemp;
			for (short i = 0; i < cell.length; i++) {
				cell[i] = row.createCell(i);
				// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
				// cell[i].setEncoding(HSSFCell.ENCODING_UTF_16);
			}
			for (int i = 0; i < titles.length; i++) {
				cell[i].setCellValue(titles[i]);
			}
			for (int i = 0; i < list.size(); i++) {
				List arrylist = new ArrayList();
				arrylist = (ArrayList) list.get(i);
				HSSFRow datarow = sheet.createRow(i + 1);
				HSSFCell data[] = new HSSFCell[titles.length];
				// System.out.println(titles.length);
				// System.out.println(listTemp.size());
				for (short j = 0; j < arrylist.size(); j++) {
					data[j] = datarow.createCell(j);
					// 将单元格定义成UTF_16编码，这样才能使输出数据不会乱码
					// data[j].setEncoding(HSSFCell.ENCODING_UTF_16);
					data[j].setCellValue(arrylist.get(j) + "");
				}
			}
			String date = DateUtils.getCurrentTime();
			OutputStream os = response.getOutputStream();
			response.setContentType("application/vnd.ms-excel;charset=gbk");
			response.setHeader("Content-Disposition", "attachment; filename="
					+ date + ".xls");
			workbook.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
}
